package cn.xz.dao.impl;

import cn.xz.dao.BaseDaoSingleton;
import cn.xz.dao.IHappyDao;
import cn.xz.entity.Happy;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class HappyDaoImpl implements IHappyDao {
    @Override
    public int addHappy(Happy happy) throws SQLException {
        Connection con = null;
        Statement sta = null;
        int count = -1;
        try {
            Class.forName("com.mysql.jdbc.Driver");
             con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sy","root","123456");
            sta = con.createStatement();
            String sql ="insert into happy values('"+happy.getId()+"','"+happy.getName()+"','"+happy.getAge()+"')";
            count = sta.executeUpdate(sql);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }finally {
            if(con!=null) {
                con.close();
            }
            if(sta!=null){
                sta.close();
            }
        }

        return count;
    }

    @Override
    public int deleteHappy(int id) throws SQLException {
        Connection con = null;
        Statement sta = null;
        int count = -1;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sy","root","123456");
            sta = con.createStatement();
            String sql ="delete from happy where id="+id;
            count = sta.executeUpdate(sql);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }finally {
            if(con!=null) {
                con.close();
            }
            if(sta!=null){
                sta.close();
            }
        }
        return count;
    }

    @Override
    public int updateHappy(Happy happy) {
        Connection con = null;
        Statement sta = null;
        int count = -1;
        int js = 0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sy","root","123456");
            sta = con.createStatement();
            String sql ="update happy set ";
            if(happy.getId()!=0){
                sql+="id='"+happy.getId()+"'";
                js = 1;
            }
            if(happy.getName()!=null){
                if(js==1){
                    sql+=",";
                }
                sql+="name='"+happy.getName()+"'";
                js = 1;
            }
            if(happy.getAge()!=0){
                if(js==1){
                    sql+=",";
                }
                sql+="age='"+happy.getAge()+"'";
                js = 1;
            }
            sql+=" where id="+happy.getId();

            if(js==1){
                count =sta.executeUpdate(sql);
            }else{
                count = 0;
            }
        }
        catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }finally {
            if(con!=null) {
                try {
                    con.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(sta!=null){
                try {
                    sta.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        return count;
    }

    @Override
    public List<Happy> getAll() {
        Connection con = null;
        Statement sta = null;
        ResultSet rs = null;
        List<Happy> happys = new ArrayList();
        int count = -1;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sy","root","123456");
            sta = con.createStatement();
            String sql ="select id,name,age from happy";
            rs = sta.executeQuery(sql);
            while(rs.next()){
                Happy happy = new Happy();
                happy.setId(rs.getInt("id"));
                happy.setName(rs.getString("name"));
                happy.setAge(rs.getInt("age"));
                happys.add(happy);
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }finally {
            if(con!=null) {
                try {
                    con.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(sta!=null){
                try {
                    sta.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        return happys;
    }

    @Override
    public Happy getHappyId(int id) {
        Connection con = null;
        Statement sta = null;
        ResultSet rs = null;
        Happy happy = null;
        try {
            con = BaseDaoSingleton.getCon();
            System.out.println(con.hashCode());
//            Class.forName("com.mysql.jdbc.Driver");
//            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sy","root","123456");
            sta = con.createStatement();
            String sql ="select id,name,age from happy";

            rs = sta.executeQuery(sql);
            if(rs.next()){
                happy=new Happy();
                happy.setId(rs.getInt("id"));
                happy.setName(rs.getString("name"));
                happy.setAge(rs.getInt("age"));
            }
        } catch ( SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDaoSingleton.closeAll(rs,sta,con);
        }

        return happy;
    }
}
